﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using stwh_Common.DBUtility;//Please add references

namespace stwh_DAL
{
    /// <summary>
    /// 数据访问类:stwh_roleinfo
    /// </summary>
    public partial class stwh_roleinfoDAL : BaseDAL
    {
        public stwh_roleinfoDAL()
        { }
        #region  BasicMethod
        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（不带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from stwh_roleinfo");
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("stwh_roleinfo", "stwh_rid", FieldColumn, FieldOrder, "stwh_rid,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（不带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount, int flag)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from stwh_roleinfo where " + If);
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("stwh_roleinfo", "stwh_rid", FieldColumn, FieldOrder, "stwh_rid,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("stwh_rid", "stwh_roleinfo");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string stwh_rname, int stwh_rid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from stwh_roleinfo");
            strSql.Append(" where stwh_rname=@stwh_rname and stwh_rid=@stwh_rid ");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rid", SqlDbType.Int,4)};
            parameters[0].Value = stwh_rname;
            parameters[1].Value = stwh_rid;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(stwh_Model.stwh_roleinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into stwh_roleinfo(");
            strSql.Append("stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate)");
            strSql.Append(" values (");
            strSql.Append("@stwh_rname,@stwh_rdescription,@stwh_rctime,@stwh_rstate,@stwh_rdelstate)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rdescription", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rctime", SqlDbType.DateTime),
					new SqlParameter("@stwh_rstate", SqlDbType.Int,4),
					new SqlParameter("@stwh_rdelstate", SqlDbType.Int,4)};
            parameters[0].Value = model.stwh_rname;
            parameters[1].Value = model.stwh_rdescription;
            parameters[2].Value = model.stwh_rctime;
            parameters[3].Value = model.stwh_rstate;
            parameters[4].Value = model.stwh_rdelstate;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 为角色设置菜单和功能权限
        /// </summary>
        /// <param name="stwh_rid">角色id</param>
        /// <param name="mids">菜单集合</param>
        /// <param name="fids">功能集合</param>
        /// <returns></returns>
        public bool Add(int stwh_rid, string mids, string fids)
        {
            string strSql = string.Format("delete from stwh_menu_role where stwh_rid = {0};delete from stwh_menu_role_function where stwh_rid = {1};", stwh_rid, stwh_rid);
            string[] strmids = mids.Split(',');
            string[] strfids = fids.Split(',');
            for (int i = 0; i < strmids.Length; i++)
            {
                strSql += "insert into stwh_menu_role values(" + strmids[i]+ "," + stwh_rid + ");";
            }
            for (int i = 0; i < strfids.Length; i++)
            {
                strSql += "insert into stwh_menu_role_function values(" + strfids[i] + "," + stwh_rid + ");";
            }
            int rows = DbHelperSQL.ExecuteSql(strSql);
            if (rows > 0) return true;
            else return false;
        }

        /// <summary>
        /// 添加一个角色信息（并且设置当前角色的所属菜单及功能）
        /// </summary>
        /// <param name="stwh_rname">角色名称</param>
        /// <param name="stwh_rdescription">描述</param>
        /// <param name="stwh_rstate">角色状态</param>
        /// <param name="stwh_menuid">所属菜单id（多个菜单id以','分割）</param>
        /// <returns></returns>
        public int Add(string stwh_rname, string stwh_rdescription, int stwh_rstate, string stwh_menuid)
        {
            int result = 0;
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@stwh_rname",SqlDbType.NVarChar,20),
                    new SqlParameter("@stwh_rdescription",SqlDbType.NVarChar,300),
                    new SqlParameter("@stwh_rstate",SqlDbType.Int,4),
                    new SqlParameter("@stwh_menuid",SqlDbType.NVarChar,100),
                    new SqlParameter("@result",SqlDbType.Int,4)};
                parameters[0].Value = stwh_rname;
                parameters[1].Value = stwh_rdescription;
                parameters[2].Value = stwh_rstate;
                parameters[3].Value = stwh_menuid;
                parameters[4].Direction = ParameterDirection.Output;
                using (DbHelperSQL.RunProcedure("ProcMenuRole", parameters))
                {
                    result = int.Parse(parameters[4].Value.ToString());
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(stwh_Model.stwh_roleinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update stwh_roleinfo set ");
            strSql.Append("stwh_rname=@stwh_rname,");
            strSql.Append("stwh_rdescription=@stwh_rdescription,");
            strSql.Append("stwh_rctime=@stwh_rctime,");
            strSql.Append("stwh_rstate=@stwh_rstate,");
            strSql.Append("stwh_rdelstate=@stwh_rdelstate");
            strSql.Append(" where stwh_rid=@stwh_rid");
            SqlParameter[] parameters = {
                    new SqlParameter("@stwh_rname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rdescription", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rctime", SqlDbType.DateTime),
					new SqlParameter("@stwh_rstate", SqlDbType.Int,4),
					new SqlParameter("@stwh_rdelstate", SqlDbType.Int,4),
					new SqlParameter("@stwh_rid", SqlDbType.Int,4)};
            parameters[0].Value = model.stwh_rname;
            parameters[1].Value = model.stwh_rdescription;
            parameters[2].Value = model.stwh_rctime;
            parameters[3].Value = model.stwh_rstate;
            parameters[4].Value = model.stwh_rdelstate;
            parameters[5].Value = model.stwh_rid;
            

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="stwh_rid">编号</param>
        /// <param name="stwh_rstate">状态</param>
        /// <returns></returns>
        public bool Update(string stwh_rid, int stwh_rstate)
        {
            string strSql = string.Format("update stwh_roleinfo set stwh_rstate={0} where stwh_rid in ({1})", stwh_rstate, stwh_rid);

            int rows = DbHelperSQL.ExecuteSql(strSql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 恢复被删除的角色
        /// </summary>
        /// <param name="stwh_rids">角色id</param>
        /// <returns></returns>
        public bool Update(string stwh_rids)
        {
            string strSql = string.Format("update stwh_roleinfo set stwh_rdelstate=0 where stwh_rid in ({0})", stwh_rids);

            int rows = DbHelperSQL.ExecuteSql(strSql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int stwh_rid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from stwh_roleinfo ");
            strSql.Append(" where stwh_rid=@stwh_rid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rid", SqlDbType.Int,4)
			};
            parameters[0].Value = stwh_rid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string stwh_rname, int stwh_rid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from stwh_roleinfo ");
            strSql.Append(" where stwh_rname=@stwh_rname and stwh_rid=@stwh_rid ");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_rid", SqlDbType.Int,4)			};
            parameters[0].Value = stwh_rname;
            parameters[1].Value = stwh_rid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据（逻辑删除，物理删除）
        /// </summary>
        /// <param name="stwh_ridlist">角色id</param>
        /// <param name="flag">标识，0逻辑删除，1物理删除</param>
        /// <returns></returns>
        public bool DeleteList(string stwh_ridlist, int flag)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format("if (select count(1) from stwh_userinfo where stwh_rid in ({0})) = 0 begin ", stwh_ridlist));
            switch (flag)
            {
                case 0: //逻辑删除
                    strSql.Append(string.Format("update stwh_roleinfo set stwh_rdelstate = 1 where stwh_rid in ({0});", stwh_ridlist));
                    break;
                default://物理删除
                    strSql.Append(string.Format("delete from stwh_menu_role_function where stwh_rid in ({0});", stwh_ridlist));
                    strSql.Append(string.Format("delete from stwh_menu_role where stwh_rid in ({0});", stwh_ridlist));
                    strSql.Append(string.Format("delete from stwh_roleinfo where stwh_rid in ({0});", stwh_ridlist));
                    break;
            }
            strSql.Append(" end");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public stwh_Model.stwh_roleinfo GetModel(int stwh_rid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 stwh_rid,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate from stwh_roleinfo ");
            strSql.Append(" where stwh_rid=@stwh_rid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rid", SqlDbType.Int,4)
			};
            parameters[0].Value = stwh_rid;

            stwh_Model.stwh_roleinfo model = new stwh_Model.stwh_roleinfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 判断角色是否有权限查看某一个页面
        /// </summary>
        /// <param name="stwh_rid">角色id</param>
        /// <param name="pString">父菜单名称（例如：sys_users、sys_setting等）</param>
        /// <param name="fString">页面（功能）名称（例如：index.aspx、add.aspx、update.aspx、add.ashx）</param>
        /// <returns></returns>
        public bool CheckRoleFunction(int stwh_rid, string pString, string fString)
        {
            bool result = false;
            SqlParameter[] parameters = {
                    new SqlParameter("@stwh_rid", SqlDbType.Int,4),
					new SqlParameter("@pString", SqlDbType.NVarChar,500),
                    new SqlParameter("@fString", SqlDbType.NVarChar,500)};

            parameters[0].Value = stwh_rid;
            parameters[1].Value = pString;
            parameters[2].Value = fString;

            using (SqlDataReader reader = DbHelperSQL.RunProcedure("ProcCheckRoleFunction", parameters))
            {
                if (reader.Read())
                {
                    if (int.Parse(reader["result"].ToString()) == 1)
                    {
                        result = true;
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public stwh_Model.stwh_roleinfo DataRowToModel(DataRow row)
        {
            stwh_Model.stwh_roleinfo model = new stwh_Model.stwh_roleinfo();
            if (row != null)
            {
                if (row["stwh_rid"] != null)
                {
                    model.stwh_rid = int.Parse(row["stwh_rid"].ToString());
                }
                if (row["stwh_rname"] != null)
                {
                    model.stwh_rname = row["stwh_rname"].ToString();
                }
                if (row["stwh_rdescription"] != null)
                {
                    model.stwh_rdescription = row["stwh_rdescription"].ToString();
                }
                if (row["stwh_rctime"] != null)
                {
                    model.stwh_rctime = DateTime.Parse(row["stwh_rctime"].ToString());
                }
                if (row["stwh_rstate"] != null)
                {
                    model.stwh_rstate = int.Parse(row["stwh_rstate"].ToString());
                }
                if (row["stwh_rdelstate"] != null)
                {
                    model.stwh_rdelstate = int.Parse(row["stwh_rdelstate"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select stwh_rid,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate ");
            strSql.Append(" FROM stwh_roleinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" stwh_rid,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate ");
            strSql.Append(" FROM stwh_roleinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM stwh_roleinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.stwh_rid desc");
            }
            strSql.Append(")AS Row, T.*  from stwh_roleinfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  BasicMethod
    }
}

